********************************************************************************************************************************
***   Replication file for:                                                                                                  ***
***   Berbee, P., Braun, S. T. and Franke, R. (2023). Reversing Fortunes of German Regions, 1926-2019.					     ***
***   							                                                                                             ***
***   SCRIPT: 	_x0_regional_classifications.do																				 ***	
***   PURPOSE: 	Reads in and prepares data on GDP 1957-2019 from different volumes										 	 ***
********************************************************************************************************************************


*** Import data

** Importing GDP data for years 1957-1992
foreach file in 1_Data1957 1_Data1961 1_Data1964 1_Data1966 2_Data1961 2_Data1968 2_Data1970 3_Data1970 3_Data1972 3_Data1974 4_Data1978 4_Data1980 5_Data1980 5_Data1992 {
import excel "$reversing/data/`file'.xlsx", firstrow clear
drop A Sheet1__La
rename T_ID labor_market_id
destring labor_market_id, replace

* Harmonize inconsistent variable names over time
rename Wohn*ev* population 
rename R*BIP* realGDP 
rename BIP* GDP* 
if inlist("`file'","1_Data1957", "1_Data1961","1_Data1964","1_Data1966") {
rename Waren* GVA_prod_total
rename Land* GVA_prod_agric
rename a*Waren* GVA_prod_industry
rename Dienst* GVA_serv_total
rename Handel* GVA_serv_trade
rename a*Dienst* GVA_serv_other
}
if inlist("`file'","2_Data1961", "2_Data1968", "2_Data1970") {
rename Warzus* GVA_prod_total
rename Landforst* GVA_prod_agric
rename Warpr* GVA_prod_industry
rename Dienstzus* GVA_serv_total
rename Handund* GVA_serv_trade
rename Ubrige* GVA_serv_other
}
if inlist("`file'","3_Data1970", "3_Data1972","3_Data1974") {
rename Warenzus* GVA_prod_total
rename Landforst* GVA_prod_agric
rename PrdesGew* GVA_prod_industry
rename Dienstlzus* GVA_serv_total
rename Handund* GVA_serv_trade
rename Ubrige* GVA_serv_other
}

* Reshape dataset to long format -> panel 
reshape long GDP, i(labor_market_id) j(year) 

* Keep only relevant variables
if (inlist("`file'","4_Data1978", "4_Data1980", "5_Data1980", "5_Data1992")) ///
keep labor_market_id year GDP population 
else if (inlist("`file'","1_Data1961")) ///
keep labor_market_id year GDP population GVA_prod_total GVA_prod_agric GVA_prod_industry GVA_serv_total GVA_serv_trade GVA_serv_other worker_*
else keep labor_market_id year GDP population GVA_prod_total GVA_prod_agric GVA_prod_industry GVA_serv_total GVA_serv_trade GVA_serv_other

* Create a variable for the number of DESTATIS publication ("gebietsstand")
gen gebietsstand=substr("`file'",1,1) 
destring gebietsstand, replace

* Harmonize population measure
replace population=population*1000 if inlist(gebietsstand,4,5) 

* Save yearly files seperately
save "$reversing/processed/intermediate/`file'.dta", replace 
}


** Import GDP and population data for years 1992-2019 (and keep values for 2000 to 2019)
foreach var in BIP EW {
import excel "$reversing/data/9_Data`var'1992_2019.xlsx", firstrow clear
drop A Sheet1__La
rename T_ID labor_market_id
destring labor_market_id, replace
gen gebietsstand=7
reshape long X, i(labor_market_id) j(year) // reshape dataset to long format -> panel 
if "`var'"=="BIP" {
rename X GDP
}
if "`var'"=="EW" {
rename X population
replace population=population*1000
}
drop if inrange(year,1992,1999)
save "$reversing/processed/intermediate/`var'0019.dta", replace 
}


** Import GDP and population data for years 1992-2009
foreach var in BIP EW {
import excel "$reversing/data/7_Data`var'1992_2009.xlsx", firstrow clear 
drop A Sheet1__La
rename T_ID labor_market_id
destring labor_market_id, replace
gen gebietsstand=6
reshape long X, i(labor_market_id) j(year) // reshape dataset to long format -> panel 
if "`var'"=="BIP" {
rename X GDP 
}
if "`var'"=="EW" {
rename X population
replace population=population*1000
}
save "$reversing/processed/intermediate/`var'9209.dta", replace 
}


** Import Gross Value Added per sector for 1992-2019 (and keep values for 2000-2019)

foreach var in BWS EW{
foreach sect in "A"  "BF" "GJ" "GT" {           //"insg." "B-E" "C" "F" "K-N" "O-T" 

if "`sect'"=="A" {
	local sectabb="A"
}
if "`sect'"=="BF" {
	local sectabb="B-F"
}
if "`sect'"=="GJ" {
	local sectabb="G-J"
}
if "`sect'"=="GT" {
	local sectabb="G-T"
}
* Import files and harmonize variables
import excel "$reversing/data/9_Data`var'_`sect'1992_2019.xlsx", firstrow clear
drop A Sheet1__La
destring T_ID, replace
rename T_ID labor_market_id
gen gebietsstand=7

* Reshape dataset to long format -> panel
reshape long X, i(labor_market_id) j(year) 

if "`var'"=="BWS" {
* Create variable names
if ("`sectabb'"=="A") rename X GVA_prod_agric
if ("`sectabb'"=="B-F") rename X GVA_prod_industry
if ("`sectabb'"=="G-T") rename X GVA_serv_total
if ("`sectabb'"=="G-J") rename X GVA_serv_trade

drop if inrange(year,1992,1999)
save "$reversing/processed/intermediate/BIP0019_`sectabb'.dta", replace 
}

if "`var'"=="EW"{
* Create variable names
if ("`sectabb'"=="A") rename X empl_prod_agric
if ("`sectabb'"=="B-F") rename X empl_prod_industry
if ("`sectabb'"=="G-T") rename X empl_serv_total
if ("`sectabb'"=="G-J") rename X empl_serv_trade

drop if inrange(year,1992,1999)
save "$reversing/processed/intermediate/EW0019_`sectabb'.dta", replace 
}
}
}


** Import Gross Value Added per sector for 1992-2009
foreach var in BWS ET{
foreach sect in "A+B" "C-F" "G-I" "G-P" {
	
* Import files and harmonize variables
import excel "$reversing/data/7_Data`var' `sect'1992_2009.xlsx", firstrow clear
drop A Sheet1__La
destring T_ID, replace
rename T_ID labor_market_id
gen gebietsstand=6

* Reshape dataset to long format -> panel
reshape long X, i(labor_market_id) j(year) 

if "`var'"=="BWS" {
* Create variable names
if ("`sect'"=="A+B") rename X GVA_prod_agric
if ("`sect'"=="C-F") rename X GVA_prod_industry
if ("`sect'"=="G-P") rename X GVA_serv_total
if ("`sect'"=="G-I") rename X GVA_serv_trade
save "$reversing/processed/intermediate/BIP9209_`sect'.dta", replace 
}
if "`var'"=="ET" {
* Create variable names
if ("`sect'"=="A+B") rename X empl_prod_agric
if ("`sect'"=="C-F") rename X empl_prod_industry
if ("`sect'"=="G-P") rename X empl_serv_total
if ("`sect'"=="G-I") rename X empl_serv_trade
save "$reversing/processed/intermediate/EW9209_`sect'.dta", replace 
}

}
}

** Import GDP deflator
import excel "$reversing/data/gdp_deflator.xlsx",sheet("Tabelle1") firstrow clear
save "$reversing/processed/intermediate/GDPdeflator.dta", replace


*** Merging data sources to one dataset

* Merging yearly files on population, GDP and sectoral GVA
use "$reversing/processed/intermediate/EW9209.dta", clear 
merge 1:1 labor_market_id year using"$reversing/processed/intermediate/BIP9209.dta", nogen

foreach var in "A+B" "C-F" "G-I" "G-P" {   
merge 1:1 labor_market_id year using"$reversing/processed/intermediate/BIP9209_`var'.dta", nogen
merge 1:1 labor_market_id year using"$reversing/processed/intermediate/EW9209_`var'.dta", nogen

}

append using "$reversing/processed/intermediate/EW0019.dta"
merge 1:1 labor_market_id year gebietsstand using"$reversing/processed/intermediate/BIP0019.dta", nogen update

foreach var in "A" "B-F" "G-J" "G-T"  {      
merge m:1 labor_market_id year using"$reversing/processed/intermediate/BIP0019_`var'.dta", nogen update
merge m:1 labor_market_id year using"$reversing/processed/intermediate/EW0019_`var'.dta", nogen update
}

foreach file in 1_Data1957 1_Data1961 1_Data1964 1_Data1966 2_Data1961 2_Data1968 2_Data1970 3_Data1970 3_Data1972 3_Data1974 4_Data1978 4_Data1980 5_Data1980 5_Data1992 {
append using "$reversing/processed/intermediate\\`file'.dta"
}


* Calculate missing GVA values
replace GVA_prod_total=GVA_prod_agric+GVA_prod_industry if inlist(gebietsstand,6,7)
replace GVA_serv_other=GVA_serv_total-GVA_serv_trade if inlist(gebietsstand,6,7)
gen empl_prod_total=empl_prod_agric+empl_prod_industry
gen empl_serv_other=empl_serv_total-empl_serv_trade

sort labor_market_id year gebietsstand
save "$reversing/processed/intermediate/GDP.dta", replace

*** EOF
